Useful Catalog Queries - Kinetica Docs

The catalog tables allow for some useful information to be extracted from the database. See Virtual Catalogs for a complete listing.

Table Resource Usage

Table RAM Usage

Kinetica Catalog

SELECT
	IF(GROUPING(source_rank) = 1,'Total',STRING(source_rank)) AS "Rank",
	SUM(size) AS "Bytes"
FROM ki_catalog.ki_tiered_objects
WHERE tier = 'RAM'
AND outer_object = '[<schema name>.]<table name>'
GROUP BY ROLLUP(source_rank)
ORDER BY INT(source_rank) NULLS LAST

Table Disk Usage

Kinetica Catalog

SELECT
	IF(GROUPING(source_rank) = 1,'Total',STRING(source_rank)) AS "Rank",
	SUM(size) AS "Bytes"
FROM ki_catalog.ki_tiered_objects
WHERE tier = 'PERSIST'
AND outer_object = '[<schema name>.]<table name>'
AND id NOT LIKE 'Wal%'
GROUP BY ROLLUP(source_rank)
ORDER BY INT(source_rank) NULLS LAST

Materialized View Dependencies

Specified Materialized View Dependencies

SELECT
    dest_table_schema||'.'||dest_table_name AS child_name,
    source_table_schema||'.'||source_table_name AS parent_name,
    source_table_kind AS parent_table_kind
FROM INFORMATION_SCHEMA.MV_DEPENDENCIES
WHERE
	mv_schema = '<mv_schema_name>' AND
	mv_name = '<mv_name>'
ORDER BY 1, 2

SELECT
    child.schema_name||'.'||child.object_name AS child_name,
    d.dep_obj_kind AS child_object_kind,
    parent.schema_name||'.'||parent.object_name AS parent_name,
    d.src_obj_kind AS parent_object_kind
FROM ki_catalog.ki_depend d
JOIN ki_catalog.ki_objects root ON
	root.oid = mv_oid AND
	schema_name = '<mv_schema_name>' AND
	object_name = '<mv_name>'
JOIN ki_catalog.ki_objects parent ON parent.oid = src_obj_oid
JOIN ki_catalog.ki_objects child ON child.oid = dep_obj_oid
ORDER BY 1, 3

All Materialized View Dependencies

SELECT
    mv_schema||'.'||mv_name AS mv_name,
    dest_table_schema||'.'||dest_table_name AS child_name,
    source_table_schema||'.'||source_table_name AS parent_name,
    source_table_kind AS parent_table_kind
FROM INFORMATION_SCHEMA.MV_DEPENDENCIES
ORDER BY 1, 2, 3

SELECT
    root.schema_name || '.' || root.object_name AS mv_name,
    child.schema_name||'.'||child.object_name AS child_name,
    d.dep_obj_kind AS child_object_kind,
    parent.schema_name||'.'||parent.object_name AS parent_name,
    d.src_obj_kind AS parent_object_kind
FROM ki_catalog.ki_depend d
JOIN ki_catalog.ki_objects root ON root.oid = mv_oid
JOIN ki_catalog.ki_objects parent ON parent.oid = src_obj_oid
JOIN ki_catalog.ki_objects child ON child.oid = dep_obj_oid
ORDER BY 1, 2, 4

10 Most Recent SQL Queries

SELECT user_name, query_text, start_time
FROM INFORMATION_SCHEMA.KI_QUERY_HISTORY
WHERE query_text <> ''
ORDER BY start_time DESC
LIMIT 10

SELECT user_name, query_text, start_time
FROM ki_catalog.ki_query_history
WHERE query_text <> ''
ORDER BY start_time DESC
LIMIT 10

Import/Export Jobs with Errors in the Past Hour

SELECT *
FROM INFORMATION_SCHEMA.KI_LOAD_HISTORY
WHERE num_error > 0 AND start_time >= TIMESTAMPADD(HOUR, -1, NOW())

SELECT *
FROM ki_catalog.ki_load_history
WHERE num_errors > 0 AND start_time >= TIMESTAMPADD(HOUR, -1, NOW())

Users with Row/Column Security Restrictions

Kinetica Catalog

SELECT
	role_name,
	object_schema_name || '.' || object_name AS object_name,
	cls AS column_level_security,
	rls AS row_level_security
FROM ki_catalog.ki_object_permissions
WHERE permission_type = 'table_read' AND (rls <> '' OR cls <> '')

Role Assignments

Kinetica Catalog

SELECT role_name AS assigned_role_name, member_name AS assignee_name
FROM ki_catalog.ki_role_members
ORDER BY 1, 2 DESC

​Table Resource Usage

​Table RAM Usage

​Table Disk Usage

​Materialized View Dependencies

​Specified Materialized View Dependencies

​All Materialized View Dependencies

​10 Most Recent SQL Queries

​Import/Export Jobs with Errors in the Past Hour

​Users with Row/Column Security Restrictions

​Role Assignments

Table Resource Usage

Table RAM Usage

Table Disk Usage

Materialized View Dependencies

Specified Materialized View Dependencies

All Materialized View Dependencies

10 Most Recent SQL Queries

Import/Export Jobs with Errors in the Past Hour

Users with Row/Column Security Restrictions

Role Assignments